# -*- coding: utf-8 -*-

import re
import datetime
from base import BaseHandler
from app.model import User, Device, CorpDevice, UserDevice, CorpUser,Warn
import time
from base import RestHandler


class DataHandler(BaseHandler):
    def post(self):
        return self.get()
    def get(self):
        if not self.current_user:
            self.finish({'code': -1, 'msg': u'没有权限'})
            return

        if self.current_user.type not in [User.TYPE_ADMIN]:
            corp = self.get_current_session().corp
            if not corp:
                self.finish({'code': -2, 'msg': u'没有权限'})
                return

            id = self.get_argument('id', '')
            if not id:
                self.finish({'code': -3, 'msg': u'id 不能为空'})
                return

            device_id = id
            q = self.session.query(CorpDevice).filter_by(corp=corp, device=device_id)
            if not self.session.query(q.exists()).scalar():
                self.finish({'code': -4, 'msg': u'没有权限'})
                return

        today = datetime.datetime.today()

        year = self.get_argument('year', str(today.year)[-2:])
        month = self.get_argument('month', '%02d' % today.month)
        limit = self.get_argument('limit', '100')
        id = self.get_argument('id', '')
        field_list = self.get_argument('field_list', '')

        if not re.match(r'^\d{2}$', year):
            self.finish({'code': 2, 'msg': u'year 格式错误'})

        if not re.match(r'^\d{2}$', month):
            self.finish({'code': 3, 'msg': u'month 格式错误'})

        if field_list:
            field_list = ['`%s`' % x.strip() for x in filter(None, field_list.split(','))]
            for f in field_list:
                if ' ' in f:
                    self.finish({'code': 4, 'msg': u'field_list 格式错误'})
                    return
        else:
            field_list = ['*']


        sql = 'select {} from z_device_data_{} where device_id = :id order by id desc limit :limit'.format(
                u','.join(field_list), year + month)
        rows = self.session.execute(sql, {'id': id, 'limit': int(limit)})
        keys = rows.keys()
        rows = rows.fetchall()
        rows = [list(r) for r in rows]

        data = {'code': 0,'data':{'body': rows, 'header': keys}}

        if int(limit) == 1:
            status = self.session.query(Device.status).filter_by(id=id).first()
            data = {'code': 0, 'status': status[0], 'data': {'body': rows, 'header': keys}}

        self.finish(data)


class FetchDataHandler(RestHandler):
    def permission(self):
       if not self.current_user:
           self.finish({'code': -1, 'msg': u'没有权限'})
           return
       action = self.request.uri.split('/')
       if action[-1] in ['position_data']:
           if self.current_user.type not in [User.TYPE_ADMIN]:
               corp = self.get_current_session().corp
               if not corp:
                   self.finish({'code': -1, 'msg': u'没有权限'})
                   return

               device_id = self.get_argument('device_id', '')
               if device_id:
                   q = self.session.query(CorpDevice).filter_by(corp=corp, device=device_id)
                   if not self.session.query(q.exists()).scalar():
                       self.finish({'code': -4, 'msg': u'没有权限'})
                       return

    def get_query(self):

        corp = self.get_current_session().corp
        q = self.session.query(CorpUser).filter_by(corp=corp,
                                                   user=self.current_user.id,
                                                   role=CorpUser.ROLE_ADMIN)

        if self.session.query(q.exists()).scalar():
            #admin
            sub = self.session.query(CorpDevice.device).filter_by(corp=self.get_current_session().corp)
        else:
            #normal
            sub = self.session.query(UserDevice.device).filter_by(user=self.current_user.id)
        return sub



    def position_data(self):
        device_id = self.get_argument('device_id','')
        field_list = self.get_argument('field_list', '')
        limit = self.get_argument('limit','')

        if not device_id:
            self.finish({'code': 1, 'msg': u'id 不能为空'})
            return

        if field_list:
            field_list = ['`%s`' % x.strip() for x in filter(None, field_list.split(','))]
            for f in field_list:
                if ' ' in f:
                    self.finish({'code':1,'msg':u'格式错误'})
        else:
            field_list = ['*']

        bt = int(self.get_argument('bt', '0'))
        et = int(self.get_argument('et', int(time.time())))
        day = datetime.datetime.fromtimestamp(et)
        year = str(day.year)[-2:]
        month = "%02d" %day.month

        if not re.match(r'^\d{2}$', year):
            self.finish({'code': 2, 'msg': u'year 格式错误'})
            return

        if not re.match(r'^\d{2}$', month):
            self.finish({'code': 3, 'msg': u'month 格式错误'})
            return

        data = []
        try:
            if limit:
                refresh_data = "select {} from z_device_data_{first} where device_id={device_id} " \
                           "and z_device_data_{first}.create <= {et} and z_device_data_{first}.create >= {bt} order by " \
                           "z_device_data_{first}.create desc limit {limit}".format(
                           u','.join(field_list),first=year + month,bt = bt,et = et,limit = limit,device_id=device_id)
            else:
                refresh_data = "select {} from z_device_data_{first} where device_id={device_id} " \
                               "and z_device_data_{first}.create <= {et} and z_device_data_{first}.create >= {bt} order by " \
                               "z_device_data_{first}.create desc".format(
                    u','.join(field_list), first=year + month, bt=bt, et=et, device_id=device_id)
            rows = self.session.execute(refresh_data)
            keys = rows.keys()
            rows = rows.fetchall()
            rows = [list(r) for r in rows]
            self.finish({'code': 0, 'data': {'body': rows, 'header': keys}})
        except:
            self.finish({'code': 1, 'Error': 'unable to fecth data'})

    def distribution_remind(self,device_id,judge_standard,field_list):

        today = datetime.datetime.today()
        year = str(today.year)[-2:]
        month = '%02d' % today.month

        if not field_list:
            field_list = 'weight1'

        remain_amounts = "select {} from z_device_data_{first} where device_id={device_id}" \
                                " order by z_device_data_{first}.create desc  " \
                               "limit 1".format(field_list, first=year + month,device_id=device_id)
        rows = self.session.execute(remain_amounts)
        a = rows.fetchone()
        if not a:
            return ''
        remain_amounts = a[0]

        curtime = "%s-%s-%s" %(today.year,today.month,today.day)
        timeArray = time.strptime(curtime, "%Y-%m-%d")
        timestamp = int(time.mktime(timeArray))

        try:
            data = []
            for i in range(0,8):
                st = timestamp - i * 24 * 3600
                et = timestamp+24*3600
                date = datetime.datetime.fromtimestamp(st)
                year = str(date.year)[-2:]
                month = "%02d" % date.month
                refresh_data = "select {} from z_device_data_{first} where device_id={device_id} and " \
                                "z_device_data_{first}.create >= {dt} and z_device_data_{first}.create <{et} " \
                               "order by z_device_data_{first}.create ".format(field_list, first=year + month,
                                dt=st,device_id=device_id, et =et)
                rows = self.session.execute(refresh_data)
                key = rows.keys()
                a = rows.fetchall()
                if a:
                    avg = a[0][0]-a[-1][0]
                    if avg>judge_standard:
                        data.append(avg)
            consume_data ={}
            if data:
                avg = round(sum(data) / len(data))
                remain_days = round(float(remain_amounts) / avg)

                consume_data['remain_days'] = remain_days
                consume_data['header'] = key
                consume_data['device_id'] = device_id
                consume_data['aver-day-consumption'] = avg
            return consume_data
        except:
            self.finish({'code': 1, 'Error': 'unable to fecth data'})

    def device_distribution_remind(self):
        judge_standard= int(self.get_argument('standard',4))
        field_list = self.get_argument('key','')
        device_id = self.get_argument('id','')
        q = self.get_query()

        if device_id:
            q = q.filter(UserDevice.device == device_id)
        data = []
        for i in q:
            a = {}
            device_data = self.distribution_remind(i[0],judge_standard,field_list)
            if device_data:
                if device_data['remain_days']<=7:
                    a['device_id'] = device_data['device_id']
                    a['remain_days'] = device_data['remain_days']
                    a['key'] = device_data['header']
                    a['aver-day-consumption'] = device_data['aver-day-consumption']
                    data.append(a)
        count = len(data)
        self.finish({'code':0,'count':count,'data':data})

    def time_format_change(self,timestamp):
        timeArray = time.localtime(timestamp)
        otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
        timeArray = time.strptime(otherStyleTime, "%Y-%m-%d %H:%M:%S")
        year = str(timeArray.tm_year)[-2:]
        month = "%02d" %timeArray.tm_mon
        return year,month


    def week_data(self):
        field_list = self.get_argument('field_list', '')
        limit = self.get_argument('limit', '')
        device_id = self.get_argument('device_id', '')
        page, per_page = self.get_page_and_per_page()

        if not device_id:
            self.finish({'code': 1, 'msg': u'device_id 不能为空'})
            return

        if field_list:
            field_list = ['`%s`' % x.strip() for x in filter(None, field_list.split(','))]
            for f in field_list:
                if ' ' in f:
                    self.finish({'code':1,'msg':u'格式错误'})
        else:
            field_list = ['*']

        today = datetime.datetime.today()
        timestamp = int(time.mktime(today.timetuple()))

        week_day = datetime.date.isoweekday(datetime.date.today())
        b_time = timestamp - 3600 * 24 * (6 + week_day)
        e_time = b_time + 3600 * 24 * 7
        b_year,b_month = self.time_format_change(b_time)
        e_year,e_month = self.time_format_change(e_time)

        try:
            if b_year!=e_year or b_month != e_month:
                refresh_data = "select {} from z_device_data_{first} where device_id={device_id} " \
                               "and z_device_data_{first}.create >= {bt} order by " \
                               "z_device_data_{first}.create desc".format(
                    u','.join(field_list), first=b_year + b_month, bt=b_time, device_id=device_id)
                rows = self.session.execute(refresh_data)
                keys = rows.keys()
                rows = rows.fetchall()
                b_data = [list(r) for r in rows]

                refresh_data = "select {} from z_device_data_{first} where device_id={device_id} " \
                               "and z_device_data_{first}.create <= {et} order by " \
                               "z_device_data_{first}.create desc".format(
                    u','.join(field_list), first=e_year + e_month, et=e_time, device_id=device_id)
                rows = self.session.execute(refresh_data)
                rows = rows.fetchall()
                e_data = [list(r) for r in rows]
                data = e_data + b_data

                if limit:
                    data = data[0:int(limit)]
                count = len(data)
                data = data[(page - 1) * per_page:page * per_page]
                self.finish({'code': 0, 'data': {'count':count,'page':page,'per_page':per_page,'body': data, 'header': keys}})


            else:
                refresh_data = "select {} from z_device_data_{first} where device_id={device_id} " \
                               "and z_device_data_{first}.create <= {et} and z_device_data_{first}.create >= {bt} order by " \
                               "z_device_data_{first}.create desc".format(
                    u','.join(field_list), first=b_year + b_month, bt=b_time, et=e_time, device_id=device_id)

                if limit:
                    refresh_data += ' limit {}'.format(int(limit))

                rows = self.session.execute(refresh_data)
                keys = rows.keys()
                rows = rows.fetchall()
                rows = [list(r) for r in rows]
                count = len(rows)
                rows = rows[(page - 1) * per_page:page * per_page]
                self.finish({'code': 0, 'data': {'count':count,'page':page,'per_page':per_page,'body': rows, 'header': keys}})

        except:
            self.finish({'code': 1, 'Error': 'unable to fecth data'})

class RunReportHandler(RestHandler):
    def permission(self):
        if not self.current_user:
            self.finish({'code': -1, 'msg': u'没有权限'})
            return

    def get_query(self):

        corp = self.get_current_session().corp
        q = self.session.query(CorpUser).filter_by(corp=corp,
                                                   user=self.current_user.id,
                                                   role=CorpUser.ROLE_ADMIN)

        if self.session.query(q.exists()).scalar():
            # admin
            sub = self.session.query(CorpDevice.device).filter_by(corp=self.get_current_session().corp)
        else:
            # normal
            sub = self.session.query(UserDevice.device).filter_by(user=self.current_user.id)


        device = self.session.query(Device.cpu_id).filter(Device.id.in_(sub),
                                              Device.status.in_([Device.STATUS_WARNING, Device.STATUS_NORMAL]))
        q = self.session.query(Warn).filter(Warn.cpu_id.in_(device))
        return q

    def warn(self):
        page, per_page = self.get_page_and_per_page()
        q = self.get_query()
        now_time = int(time.time())
        timestamp = now_time - now_time % 86400 + time.timezone

        if 'type' in self.p :type = self.p.type
        else:type = ''
        if 'cpu_id' in self.p :cpu_id = self.p.cpu_id
        else:cpu_id = ''
        if cpu_id:
            cpu_id_list = [i.strip() for i in filter(None,cpu_id.split(','))]
            q = q.filter(Warn.cpu_id.in_(cpu_id_list))

        cur_time = int(time.time())
        if type == "today":
            b_time = timestamp
            e_time = cur_time
        elif type == "day":
            b_time = timestamp - 24*3600
            e_time = timestamp
        elif type == "week":
            b_time = timestamp - 3600 * 24 * 7
            e_time = timestamp
        elif type == "month":
            b_time = timestamp - 3600 * 24 *30
            e_time = timestamp
        else:
            b_time = 0
            e_time = cur_time
        data = []

        if 'b_time' in self.p: b_time=self.p['b_time']
        if 'e_time' in self.p: e_time = self.p['e_time']
        warn = q.filter(Warn.time >= b_time, Warn.time < e_time).order_by(Warn.time.desc())
        warn_list = [i.dict() for i in warn if i.dict()['code']]
        count = len(warn_list)
        device_count = {}
        for i in warn_list:
            warn_dict = {}
            q = self.session.query(Device).filter_by(cpu_id=i["cpu_id"]).first()
            device_name = q.name
            warn_dict['cpu_id'] = i['cpu_id']
            warn_dict['code'] = i['code']
            warn_dict['content'] = i['content']
            warn_dict['create'] = i['create']
            warn_dict['time'] = i['time']
            warn_dict['status'] = i['status']
            warn_dict['name'] = device_name
            if device_name in device_count.keys():
                device_count[device_name] += 1
            else:
                device_count[device_name] = 1
            warn_dict['warn_contrast'] = i['warn_contrast_obj']['content']
            data.append(warn_dict)
        data = data[(page - 1) * per_page:page* per_page]
        self.finish({'code': 0, 'page':page,'per_page':per_page,'warn_count': count,"itemlist":data,"device_count":device_count})



